package com.cap.bts.crmdp.dealer.remind.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.inject.Inject;

import org.apache.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.engine.SessionFactoryImplementor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.remind.dao.ICustomerDao;
import com.cap.bts.crmdp.dealer.remind.domain.Customer;
import com.cap.bts.crmdp.dealer.remind.dto.CustomerDto;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;

@Repository("customerDao")
public class CustomerDaoImpl extends BaseDaoImpl<Customer, Long> implements ICustomerDao {
	
	@Inject
	public CustomerDaoImpl(@Value("com.cap.bts.crmdp.dealer.remind.domain.Customer") Class<Customer> clazz) {
		super(clazz);
	}
	
	private static final Logger LOGGER = Logger.getLogger(CustomerDaoImpl.class);
	
	@Override
	public Customer getCustomerByCustomerID(String customerID) throws AppException {
		String sql = "select * from tm_raw_customer where customer_id='" + customerID + "'";
		List<Customer> list = this.getListByNativeQuery(sql);
		if(list != null && !list.isEmpty()){
			Customer customer = list.get(0);
			return customer;
		}
		return null;
	}

	@Override
	public void deleteCustomer() throws AppException {
		String sql = "delete from tm_raw_customer";
		this.getJpaTemplate().getEntityManager().createNativeQuery(sql).executeUpdate();
	}

	@Override
	public void deleteAllExistCustomers(List<String> customerIdList) throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		
		try {
			LOGGER.info("start delete customer");
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			pstmt = connection.prepareStatement("delete from tm_raw_customer where customer_id = ?");
			for (int i = 0; i < customerIdList.size(); i++) {
				pstmt.setString(1, customerIdList.get(i));
				pstmt.addBatch();
			}
			
			LOGGER.info(pstmt);
			LOGGER.info("start delete commit");
			pstmt.executeBatch();     
			connection.commit();  
			LOGGER.info("end delete commit");
		} catch (Exception e) {
			throw new AppException("ERROR", e);
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
				LOGGER.info("end delete customer");
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
	}

	@Override
	public void insertAllCustomers(List<CustomerDto> list) throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		
		try {
			LOGGER.info("start insert customer");
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			int i=0;
			pstmt = connection.prepareStatement("INSERT INTO tm_raw_customer  ( " +
			        "    service_center_id , " +
			        "    customer_id , " +
			        "    mobile_number , " +
			        "    customer_name , " +
			        "    contact_name , " +
			        "    contact_phone , " +
			        "    creation_date , " +
			        "    last_modification_date , " +
			        "    receive_SMS_or_Not , " +
			        "    crm_member," +
			        "    customer_type," +
			        "    create_by," +
			        "    create_date," +
			        "    last_update_by," +
			        "    last_update_date" +
			        ") " +
			        "VALUES " +
			        "   ( " +
			        "   ? ,  ? , ? ,  ? ,  ? , " + 
			        "  	? ,  ? , ? ,  ? ,  ?,"
			        + " ?,?,?,?,?  " +
			        "   ) ");
			for(CustomerDto customerDto:list){
				i++;
				if(customerDto.getServiceCenterID() != null){
					pstmt.setLong(1, customerDto.getServiceCenterID());
				}else{
					pstmt.setNull(1, Types.BIGINT);
				}
				pstmt.setString(2, customerDto.getCustomerID());
				pstmt.setString(3, customerDto.getMobileNumber());
				pstmt.setString(4, customerDto.getName());
				pstmt.setString(5, customerDto.getContactName());
				pstmt.setString(6, customerDto.getContactPhone());
				if(customerDto.getCreationDate() != null){
					pstmt.setDate(7, new Date(customerDto.getCreationDate().getTime()));
				}else{
					pstmt.setDate(7,null);
				}
				if(customerDto.getLastModificationDate() != null){
					pstmt.setDate(8, new Date(customerDto.getLastModificationDate().getTime()));
				}else{
					pstmt.setDate(8,null);
				}
				pstmt.setString(9, customerDto.getReceiveSMSorNot());
				pstmt.setString(10, customerDto.getCrmMember());
				pstmt.setString(11, customerDto.getCustomerType());
				pstmt.setLong(12, 0);
				pstmt.setDate(13, new Date(new java.util.Date().getTime()));
				pstmt.setLong(14, 0);
				pstmt.setDate(15, new Date(new java.util.Date().getTime()));
				pstmt.addBatch();
//				LOGGER.info(i);
				if(i%1000 == 0){
					LOGGER.info("start commit");
					pstmt.executeBatch();     
					connection.commit();     
					LOGGER.info("end commit");
					pstmt.clearBatch(); 
				}
				
			}
			
			if(i%1000 != 0){
				LOGGER.info("start commit");
				pstmt.executeBatch();     
				connection.commit();  
				LOGGER.info("end commit");
			}
		} catch (Exception e) {
			throw new AppException("ERROR", e);
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
				LOGGER.info("end insert customer");
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
		
	}

}
